# -*- coding: utf-8 -*-
from scpy.logger import get_logger
import os
import sys
import tornado
from tornado.concurrent import run_on_executor
from tornado.web import HTTPError
from util.pgutil import PgUtil, Pgutil_Local
import json
import numpy as np
from collections import Counter
import datetime
from server_util import *

reload(sys)
sys.setdefaultencoding('utf-8')

logger = get_logger(__file__)

CURRENT_PATH = os.path.dirname(__file__)
if CURRENT_PATH:
    CURRENT_PATH = CURRENT_PATH + "/"

pg = PgUtil()


class DBBasedHandler(tornado.web.RequestHandler):
    def build_query_params(self, **kwargs):
        """
        构建查询数据库的参数collection和filter
        :rtype : object
        """
        pass

    def handle_item(self, item):
        """
        从每条数据库记录到返回数据的默认实现，如有需要可在子类重写此方法
        :param dict item: 每条数据库的记录
        :return dict: 实际数据
        """
        if 'updateTime' in item:
            del item['updateTime']
        return item

    def _get_page_and_size(self):
        """
        ATT：不要重写此方法！！！
        """
        try:
            page = int(self.get_argument('page', 1))
            size = int(self.get_argument('size', 20))
            if not 1 < size <= 100:
                raise HTTPError(400, 'bad request.')
        except:
            raise HTTPError(400, 'bad request.')
        return page, size

    def build_return_data(self, **kwargs):
        result = {
            'total': kwargs['total'],
            'totalPage': kwargs['total_page'],
            'currentPage': kwargs['current_page'],
            'pageSize': kwargs['page_size'],
            'data': []
        }
        if self.NEED_PARAMS:
            result[self.NEED_PARAMS[0][0]] = kwargs['params'][self.NEED_PARAMS[0][0]]
        for item in kwargs['data']:
            result['data'].append(self.handle_item(item))
        return result

    @run_on_executor
    def get(self):
        pass

    def trans_resullt_fromat(self,result_list):
        return_result = []
        for item in result_list:
            return_result.append({
                'id': item.get('id',''),
                'url': item.get('url',''),
                'companyName': item.get('company_name', ''),
                'categoryType': item.get('category_type', ''),
                'category': item.get('category', ''),
                'salary': item.get('salary'),
                'city': item.get('city', ''),
                'workingYears': item.get('working_years'),
                'degree': item.get('degree',''),
                'jobTitle': item.get('job_title', ''),
                'description': item.get('description',''),
                'source': item.get('source',''),
                'companyUrl': item.get('company_url',''),
                'entity': item.get('item',''),
                'welfare': item.get('welfare',[]),
                'keywords': item.get('keywords',[]),
                'logo': item.get('logo',''),
                'scale': item.get('scale',''),
                'releaseTime':  item.get('release_time','').strftime('%Y-%m-%d'),
                'updateTime': item.get('update_time').strftime('%Y-%m-%d'),
                'jobNature': item.get('job_nature',''),
                'industry': item.get('industry',''),
                'position': item.get('position',''),
                'requireNum': item.get('require_num','')
            })
        return return_result

class GroupMonthInfo(DBBasedHandler):

    def get(self):
        company_name = self.get_argument('companyName')
        year_text = self.get_argument('year', datetime.datetime.now().year)
        month_text = self.get_argument('month', datetime.datetime.now().month)
        if year_text==None or year_text=='':
            year_text =  datetime.datetime.now().year
        if month_text==None or month_text=='':
            month_text = datetime.datetime.now().month
        data = self.build_return_data(
            companyName=company_name,
            yearText=year_text,
            monthText=month_text
        )
        if len(data):
            self.set_header('Content-Type', 'application/json;chartset=UTF-8')
            self.write(json.dumps(data, ensure_ascii=False, sort_keys=True, indent=4))
        else:
            raise HTTPError(404, 'not found.')

    def build_return_data(self, **kwargs):
        company_name = kwargs['companyName']
        year = kwargs['yearText']
        month = kwargs['monthText']
        time_text_list = self.get_time_step(company_name,year,month)
        return_data = {}
        recruitment_data = {}
        salary_avg_data = {}
        resume_data = {}
        for time_text in time_text_list:
            data = {}
            # category type part
            query_sql = """
                        select * from recruitment
                        where company_name=%s and
                        to_char(release_time,'YYYYMM')=%s and
                        category_type not in
                        (select category_type from recruitment where
                        company_name=%s and to_char(release_time,'YYYYMM')<%s)
                        and category_type<>'' and category_type is not null
                        """
            values = (company_name, time_text, company_name, time_text)
            result = pg.select_all_sql(query_sql, values)
            if len(result):
                # data['addCategoryTypeList'] = self.trans_resullt_fromat(result)
                data['addCategoryTypeText'] = list(set([item['category_type'] for item in result]))
            else:
                # data['addCategoryTypeList'] = []
                data['addCategoryTypeText'] = []

            # category part
            query_sql = """
                        select * from recruitment
                        where company_name=%s and
                        to_char(release_time,'YYYYMM')=%s and
                        category not in
                        (select category from recruitment where
                        company_name=%s and to_char(release_time,'YYYYMM')<%s)
                        and category<>'' and category is not null
                        """
            values = (company_name, time_text, company_name, time_text)
            result = pg.select_all_sql(query_sql, values)
            if len(result):
                # data['addCategoryList'] = self.trans_resullt_fromat(result)
                data['addCategoryText'] = list(set([item['category'] for item in result]))
            else:
                # data['addCategoryList'] = []
                data['addCategoryText'] = []

            # location part
            query_sql = """
                        select * from recruitment
                        where company_name=%s and
                        to_char(release_time,'YYYYMM')=%s
                        and city not in
                        (select city from recruitment where
                        company_name=%s and to_char(release_time,'YYYYMM')<%s)
                        and city<>'' and city is not null
                        """
            values = (company_name, time_text, company_name, time_text)
            result = pg.select_all_sql(query_sql, values)
            if len(result):
                # data['addLocationList'] = self.trans_resullt_fromat(result)
                data['addLocationText'] = list(set([item['city'] for item in result]))
            else:
                # data['addLocationList'] = []
                data['addLocationText'] = []

            ## 2017-01-14
            ## salary part
            # query_sql = """
            #             select
            #             case
            #             when a.avg<(b.avg- b.stddev_pop) then 'Lower'
            #             when a.avg>(b.avg+ b.stddev_pop) then 'Higher'
            #             when a.avg>(b.avg- b.stddev_pop) and  a.avg<(b.avg- b.stddev_pop)  then 'Normal'
            #             else
            #                 'Empty'
            #             end as status, a.avg, b.avg from
            #             (select avg(salary)from recruitment where
            #              company_name=%s and
            #              to_char(release_time,'YYYYMM')=%s) a,
            #             (select avg(salary) ,stddev_pop(salary) from recruitment where
            #              company_name=%s and
            #              to_char(release_time,'YYYYMM')<%s) b
            #             """
            # values = (company_name, time_text, company_name, time_text)
            # result = pg.select_all_sql(query_sql, values)
            # if len(result):
            #     data['salaryDetect'] = {
            #         'des':result[0][0],
            #         'salaryAvg':result[0][1],
            #         'preSalaryAvg':result[0][2],
            #     }
            # else:
            #     data['salaryDetect'] = {}

            # # 2017-01-14
            # # working years part
            # query_sql = """
            #             select
            #             case
            #             when a.avg<(b.avg- b.stddev_pop) then 'Lower'
            #             when a.avg>(b.avg+ b.stddev_pop) then 'Higher'
            #             when a.avg>(b.avg- b.stddev_pop) and  a.avg<(b.avg- b.stddev_pop)  then 'Normal'
            #             else
            #                 'Empty'
            #             end as status, a.avg, b.avg from
            #             (select avg(working_years)from recruitment where
            #              company_name=%s and
            #              to_char(release_time,'YYYYMM')=%s) a,
            #             (select avg(working_years) ,stddev_pop(working_years) from recruitment where
            #              company_name=%s and
            #              to_char(release_time,'YYYYMM')<%s) b
            #             """
            # values = (company_name, time_text, company_name, time_text)
            # result = pg.select_all_sql(query_sql, values)
            # if len(result):
            #     data['workingYearsDetect'] = {
            #         'des':result[0][0],
            #         'workingYearsAvg':result[0][1],
            #         'preWorkingYearsAvg':result[0][2],
            #     }
            # else:
            #     data['workingYearsDetect'] = {}
            #
            recruitment_data[time_text] = data


            # salary avg
            query_sql = """
                        select avg(salary) from recruitment
                        where company_name=%s and
                        to_char(release_time,'YYYYMM')=%s
                        """
            values = (company_name, time_text)
            result = pg.select_sql(query_sql, values)
            if len(result):
                salary_avg_data[time_text] = result[0] if result[0] else 0
            else:
                salary_avg_data[time_text] = 0

            # resume dis
            query_sql = """
                        select category,count(category) from resume
                        where company_name=%s and
                        to_char(end_time,'YYYYMM')=%s
                        and category is not null and category<>'' group by category
                        """
            values = (company_name, time_text)
            result = pg.select_all_sql(query_sql, values)
            resume_data[time_text] = {}
            if len(result):
                count = 0
                resume_data[time_text]['categoryDis'] = []
                for item in result:
                    resume_data[time_text]['categoryDis'].append({
                        'name': item[0],
                        'value': item[1]
                    })
                    count += item[1]
                resume_data[time_text]['count'] = count
            else:
                resume_data[time_text]['categoryDis'] = []
                resume_data[time_text]['count'] = 0

        # build return data format
        for key,item in recruitment_data.items():
            if len(item['addCategoryTypeText'])==0 and len(item['addCategoryText'])==0 and len(item['addLocationText'])==0:
                recruitment_data.pop(key)
        return_data['recruitmentDetect'] = recruitment_data
        return_data['salaryAvg'] = salary_avg_data
        return_data['resumeDis'] = resume_data
        return return_data

    def get_time_step(self, company_name, year_text, month_text):
        query = """
                select release_time from recruitment
                where company_name=%s
                order by release_time limit 1
                """
        values = (company_name,)
        result = pg.select_all_sql(query, values)
        time_step_list = list()
        if len(result):
            begin_time = result[0][0]
            end_time = datetime.datetime.strptime(str(year_text)+str(month_text), '%Y%m')
            time_delta = datetime.timedelta(days=30)
            while end_time>begin_time:
                time_step_list.append(end_time.strftime("%Y%m"))
                end_time -= time_delta
            if end_time.month == begin_time.month:
                time_step_list.append(begin_time.strftime("%Y%m"))
            return time_step_list
        else:
            return time_step_list

class CompanyResumeSearchHandler(DBBasedHandler):
    def get(self):
        company_name = self.get_argument('companyName')
        from_date_text = self.get_argument('from', '')
        to_date_text = self.get_argument('to', '')
        # page, size = self._get_page_and_size()
        data = self.build_return_data(
            companyName=company_name,
            fromText=from_date_text,
            toText=to_date_text,
        )
        if len(data):
            self.set_header('Content-Type', 'application/json;chartset=UTF-8')
            self.write(json.dumps(data, ensure_ascii=False, sort_keys=True, indent=4))
        else:
            raise HTTPError(404, 'not found.')

    def build_return_data(self, **kwargs):
        company_name = kwargs['companyName']
        # return_data = dict()
        from_time = kwargs['fromText']
        to_time = kwargs['toText']
        if from_time != '' and to_time != '':
            query_sql = """
                        select * from resume where to_char(end_time,'YYYYMMDD')>=%s
                        and to_char(end_time,'YYYYMMDD')<=%s and company_name=%s
                        order by to_char(end_time,'YYYYMMDD') desc
                        """
            values = (from_time, to_time, company_name)
            result = pg.select_all_sql(query_sql, values)
        else:
            query_sql = "select * from resume where company_name=%s order by to_char(end_time,'YYYYMMDD') desc"
            values = (company_name,)
            result = pg.select_all_sql(query_sql, values)

        result_trans = list()
        # trans datetime format
        for item in result:
            result_trans.append({
                'id': item.get('id',''),
                'url': item.get('url',''),
                'companyName': item.get('company_name', ''),
                'workingYears': item.get('working_years'),
                'degree': item.get('degree',''),
                'sex': item.get('sex',''),
                'location': item.get('location',''),
                'jobTitle': item.get('job_title', ''),
                'detail': item.get('detail',''),
                'preCompany': item.get('pre_company',''),
                'nextCompany': item.get('next_company',''),
                'releaseTime':  item.get('release_time','').strftime('%Y-%m-%d'),
                'beginTime': item.get('begin_time').strftime('%Y-%m-%d'),
                'endTime': item.get('end_time').strftime('%Y-%m-%d'),
                'dob': item.get('dob').strftime('%Y-%m-%d'),
                'school': item.get('school',''),
                'major': item.get('major',''),
            })
        return_data = {
            'data': result_trans,
            'totalLength': len(result_trans)
        }
        return return_data


class CompanyRecruimentSearchHandler(DBBasedHandler):
    def get(self):
        company_name = self.get_argument('companyName')
        from_date_text = self.get_argument('from', '')
        to_date_text = self.get_argument('to', '')
        # page, size = self._get_page_and_size()
        data = self.build_return_data(
            companyName=company_name,
            fromText=from_date_text,
            toText=to_date_text,
        )
        if len(data):
            self.set_header('Content-Type', 'application/json;chartset=UTF-8')
            self.write(json.dumps(data, ensure_ascii=False, sort_keys=True, indent=4))
        else:
            raise HTTPError(404, 'not found.')

    def build_return_data(self, **kwargs):
        company_name = kwargs['companyName']
        # return_data = dict()
        from_time = kwargs['fromText']
        to_time = kwargs['toText']
        if from_time != '' and to_time != '':
            query_sql = """
                        select * from recruitment where to_char(release_time,'YYYYMMDD')>=%s
                        and to_char(release_time,'YYYYMMDD')<=%s and company_name=%s
                        order by to_char(release_time,'YYYYMMDD') desc
                        """
            values = (from_time, to_time, company_name)
            result = pg.select_all_sql(query_sql, values)
        else:
            query_sql = "select * from recruitment where company_name=%s order by to_char(release_time,'YYYYMMDD') desc"
            values = (company_name,)
            result = pg.select_all_sql(query_sql, values)

        result_trans = list()
        # trans datetime format
        for item in result:
            result_trans.append({
                'id': item.get('id',''),
                'url': item.get('url',''),
                'companyName': item.get('company_name', ''),
                'categoryType': item.get('category_type', ''),
                'category': item.get('category', ''),
                'salary': item.get('salary'),
                'city': item.get('city', ''),
                'workingYears': item.get('working_years'),
                'degree': item.get('degree',''),
                'jobTitle': item.get('job_title', ''),
                'description': item.get('description',''),
                'source': item.get('source',''),
                'companyUrl': item.get('company_url',''),
                'entity': item.get('item',''),
                'welfare': item.get('welfare',[]),
                'keywords': item.get('keywords',[]),
                'logo': item.get('logo',''),
                'scale': item.get('scale',''),
                'releaseTime':  item.get('release_time','').strftime('%Y-%m-%d'),
                'updateTime': item.get('update_time').strftime('%Y-%m-%d'),
                'jobNature': item.get('job_nature',''),
                'industry': item.get('industry',''),
                'position': item.get('position',''),
                'requireNum': item.get('require_num',''),
                'address': item.get('address',''),
                'salaryText': item.get('salary_text','')
            })

        return_data = {
            'data': result_trans,
            'totalLength': len(result_trans)
        }
        return return_data


class CompanyRecruitmentDetectorHandler(DBBasedHandler):
    def get(self):
        company_name = self.get_argument('companyName')
        from_date_text = self.get_argument('from', '')
        to_date_text = self.get_argument('to', '')
        data = self.build_return_data(
            companyName=company_name,
            fromText=from_date_text,
            toText=to_date_text
        )
        if len(data):
            self.set_header('Content-Type', 'application/json;chartset=UTF-8')
            self.write(json.dumps(data, ensure_ascii=False, sort_keys=True, indent=4))
        else:
            raise HTTPError(404, 'not found.')

    def build_return_data(self, **kwargs):
        """

        :rtype : object
        """
        company_name = kwargs['companyName']
        from_time = kwargs['fromText']
        to_time = kwargs['toText']

        # pre recruitment info
        pre_query_sql = "select * from recruitment where to_char(release_time,'YYYYMMDD')<%s and company_name=%s"
        pre_values = (from_time, company_name)
        pre_result = pg.select_all_sql(pre_query_sql, pre_values)
        if len(pre_result):
            pre_info = self.cal_month_info(pre_result)
        else:
            raise HTTPError(404, 'no recruitment information before %s' % from_time)
        # query recruitment info
        query_sql = "select * from recruitment where to_char(release_time,'YYYYMMDD')>=%s and to_char(release_time,'YYYYMMDD')<=%s and company_name=%s"
        values = (from_time, to_time, company_name)
        result = pg.select_all_sql(query_sql, values)
        if len(result):
            pre_info = self.cal_month_info(pre_result)
        else:
            raise HTTPError(404, 'no recruitment information between %s and %s' % (from_time, to_time))
        info = self.cal_month_info(result)

        # build return dic
        compare_result_dic = self.month_recruitment_info_compare(pre_info, info)
        return compare_result_dic

    def month_recruitment_info_compare(self, pre_month_info, this_month_info):
        compareResultDict = dict()
        # salary compare
        if this_month_info.get('salaryAvg') >= (pre_month_info.get('salaryAvg') + pre_month_info.get('salaryStd')):
            compareResultDict['salaryDes'] = u'招聘工资值偏高'
            compareResultDict['salaryDetect'] = 'Higher'
        elif this_month_info.get('salaryAvg') <= (pre_month_info.get('salaryAvg') - pre_month_info.get('salaryStd')):
            compareResultDict['salaryDes'] = u'招聘工资值偏低'
            compareResultDict['salaryDetect'] = 'Lower'
        else:
            compareResultDict['salaryDes'] = u'招聘工资值在正常范围内'
            compareResultDict['salaryDetect'] = 'Normal'
        # working years compare
        if this_month_info.get('workingYearsAvg') >= (
            pre_month_info.get('workingYearsAvg') + pre_month_info.get('workingYearsStd')):
            compareResultDict['workingYearsDes'] = u'招聘工作经验要求偏高'
            compareResultDict['workingYearsDetect'] = 'Higher'
        elif this_month_info.get('workingYearsAvg') <= (
            pre_month_info.get('workingYearsAvg') - pre_month_info.get('workingYearsStd')):
            compareResultDict['workingYearsDes'] = u'招聘工作经验要求偏低'
            compareResultDict['workingYearsDetect'] = 'Lower'
        else:
            compareResultDict['workingYearsDes'] = u'招聘工作经验要求在正常范围内'
            compareResultDict['workingYearsDetect'] = 'Normal'
        # counter compare
        # city
        city_compare_dict = self.compare_dict(pre_month_info.get('cityInfo'), this_month_info.get('cityInfo'))
        compareResultDict['cityAddingDetect'] = city_compare_dict['addingKeys']
        if len(city_compare_dict['addingKeys']) != 0:
            compareResultDict['cityAddingDes'] = u'新增工作地点为:'
            for city in city_compare_dict['addingKeys']:
                compareResultDict['cityAddingDes'] += city + ' '
        if len(city_compare_dict['sameKeysDetection']) != 0:
            compareResultDict['cityDetectionDes'] = u'工作地点分布出现较大变化:'
            for item in city_compare_dict['sameKeysDetection']:
                if item['percentageDiff'] < 0:
                    compareResultDict['cityDetectionDes'] += u'%s 地区职位比例大量减少' % item['key']
                if item['percentageDiff'] > 0:
                    compareResultDict['cityDetectionDes'] += u'%s 地区职位比例大量增加' % item['key']
        # category
        category_compare_dict = self.compare_dict(pre_month_info.get('categoryInfo'),
                                                  this_month_info.get('categoryInfo'))
        compareResultDict['categoryAddingDetect'] = category_compare_dict['addingKeys']
        if len(category_compare_dict['addingKeys']) != 0:
            compareResultDict['categoryAddingDes'] = u'新增职位类型:'
            for category in category_compare_dict['addingKeys']:
                compareResultDict['categoryAddingDes'] += category + ' '
        if len(category_compare_dict['sameKeysDetection']) != 0:
            compareResultDict['categoryDetectionDes'] = u'职位类型需求出现较大变化:'
            for item in category_compare_dict['sameKeysDetection']:
                if item['percentageDiff'] < 0:
                    compareResultDict['categoryDetectionDes'] += u'%s 职位类型比例大量减少' % item['key']
                if item['percentageDiff'] > 0:
                    compareResultDict['categoryDetectionDes'] += u'%s 职位类型比例大量增加' % item['key']
        # category_type
        category_type_compare_dict = self.compare_dict(pre_month_info.get('categoryTypeInfo'),
                                                       this_month_info.get('categoryTypeInfo'))
        compareResultDict['categoryTypeAddingDetect'] = category_type_compare_dict['addingKeys']
        if len(category_type_compare_dict['addingKeys']) != 0:
            compareResultDict['categoryTypeAddingDes'] = u'新增职位方向:'
            for category_type in category_type_compare_dict['addingKeys']:
                compareResultDict['categoryTypeAddingDes'] += category_type + ' '
        if len(category_type_compare_dict['sameKeysDetection']) != 0:
            compareResultDict['categoryTypeDetectionDes'] = u'职位方向需求出现较大变化:'
            for item in category_type_compare_dict['sameKeysDetection']:
                if item['percentageDiff'] < 0:
                    compareResultDict['categoryTypeDetectionDes'] += u'%s 职位方向比例大量减少' % item['key']
                if item['percentageDiff'] > 0:
                    compareResultDict['categoryTypeDetectionDes'] += u'%s 职位方向比例大量增加' % item['key']
        return compareResultDict

    def cal_month_info(self, month_result_list):
        salaryList = list()
        degreeList = list()
        workingYearsList = list()
        categoryTypeList = list()
        categoryList = list()
        cityList = list()
        preInfo = dict()
        for infoDic in month_result_list:
            salaryList.append(infoDic.get('salary'))
            degreeList.append(infoDic.get('degree'))
            workingYearsList.append(infoDic.get('working_years'))
            categoryTypeList.append(infoDic.get('category_type'))
            categoryList.append(infoDic.get('category'))
            cityList.append(infoDic.get('city'))
        preInfo['salaryArray'] = np.array(salaryList)
        if len(salaryList) < 1:
            preInfo['salaryAvg'] = None
            preInfo['salaryStd'] = None
        else:
            preInfo['salaryAvg'] = np.average(preInfo['salaryArray'])
            preInfo['salaryStd'] = np.std(preInfo['salaryArray'])
        preInfo['degreeInfo'] = Counter(degreeList)
        preInfo['workingYearsArray'] = np.array(workingYearsList)
        if len(workingYearsList) < 1:
            preInfo['workingYearsAvg'] = None
            preInfo['workingYearsStd'] = None
        else:
            preInfo['workingYearsAvg'] = np.average(preInfo['workingYearsArray'])
            preInfo['workingYearsStd'] = np.std(preInfo['workingYearsArray'])
        preInfo['categoryTypeInfo'] = Counter(categoryTypeList)
        preInfo['categoryInfo'] = Counter(categoryList)
        preInfo['cityInfo'] = Counter(cityList)

        return preInfo

    def compare_dict(self, pre_dict, this_dict):
        pre_dict_keys = pre_dict.keys()
        this_dict_keys = this_dict.keys()
        same_keys_list = list()
        missing_keys_list = list()
        add_keys_list = list()
        for pre_item in pre_dict_keys:
            if pre_item in this_dict_keys:
                same_keys_list.append(pre_item)
                this_dict_keys.remove(pre_item)
            else:
                missing_keys_list.append(pre_item)
        add_keys_list = this_dict_keys
        # percentage detect
        same_keys_detection = list()
        for key in same_keys_list:
            pre_key_percentage = pre_dict[key] / float(sum(pre_dict.values()))
            this_key_percentage = this_dict[key] / float(sum(this_dict.values()))
            if (this_key_percentage - pre_key_percentage) > 0.25 or (this_key_percentage - pre_key_percentage) < -0.25:
                same_keys_detection.append({
                    'key': key,
                    'percentageDiff': (this_key_percentage - pre_key_percentage),
                })
        return {
            'sameKeys': same_keys_list,
            'missingKeys': missing_keys_list,
            'addingKeys': add_keys_list,
            'sameKeysDetection': same_keys_detection,
        }


class CompanyResumeStatisticHandler(DBBasedHandler):
    def get(self):
        company_name = self.get_argument('companyName')
        from_date_text = self.get_argument('from', '20000101')
        to_date_text = self.get_argument('to', '20200101')
        if from_date_text==None or from_date_text=='':
            from_date_text = '20000101'
        if to_date_text==None or to_date_text=='':
            to_date_text = '20200101'
        data = self.build_return_data(
            companyName=company_name,
            fromText=from_date_text,
            toText=to_date_text
        )
        if len(data):
            self.set_header('Content-Type', 'application/json;chartset=UTF-8')
            self.write(json.dumps(data, ensure_ascii=False, sort_keys=True, indent=4))
        else:
            raise HTTPError(404, 'not found.')

    def build_return_data(self, **kwargs):
        """

        :rtype : object
        """
        company_name = kwargs['companyName']
        from_time = kwargs['fromText']
        to_time = kwargs['toText']
        if from_time == '' or to_time == '':
            raise HTTPError(400, 'time format failed')
        else:
            query_sql = "select * from resume where to_char(release_time,'YYYYMMDD')>=%s and to_char(release_time,'YYYYMMDD')<=%s and company_name=%s"
            values = (from_time, to_time, company_name)
            result = pg.select_all_sql(query_sql, values)
            resume_info = self.cal_resume_info(result)
        return resume_info

    def cal_resume_info(self, resume_list):
        resume_analysis = dict()
        resume_analysis['schoolInfo'] = {
            '985/211本科院校': 0,
            '普通本科院校': 0,
            '民办本科院校': 0,
            '专科院校': 0,
            '其他院校': 0,
        }
        sex_list = list()
        degree_list = list()
        major_list = list()
        school_list = list()
        job_title_list = list()
        pre_company_list = list()
        next_company_list = list()
        category_list = list()
        category_type_list = list()
        for resume_item in resume_list:
            if resume_item.get('sex'):
                sex_list.append(resume_item['sex'])
            if resume_item.get('degree'):
                degree_list.append(resume_item['degree'])
            if resume_item.get('major'):
                major_list.append(resume_item['major'][0])
            if resume_item.get('school'):
                school_list.append(resume_item['school'][0])
            if resume_item.get('job_title'):
                job_title_list.append(resume_item['job_title'])
            if resume_item.get('pre_company'):
                pre_company_list.append(resume_item['pre_company'])
            if resume_item.get('next_company'):
                next_company_list.append(resume_item['next_company'])
            if resume_item.get('category'):
                category_list.append(resume_item['category'])
            if resume_item.get('category_type'):
                category_type_list.append(resume_item['category_type'])

        resume_analysis['sexInfo'] = trans_counter_to_key_value(Counter(sex_list))
        resume_analysis['degreeInfo'] = trans_counter_to_key_value(Counter(degree_list))
        resume_analysis['majorInfo'] = trans_counter_to_key_value(Counter(major_list))
        resume_analysis['jobTitleInfo'] = trans_counter_to_key_value(Counter(job_title_list))
        resume_analysis['preCompanyInfo'] = trans_counter_to_key_value(Counter(pre_company_list))
        resume_analysis['nextCompanyInfo'] = trans_counter_to_key_value(Counter(next_company_list))
        resume_analysis['categoryInfo'] = trans_counter_to_key_value(Counter(category_list))
        resume_analysis['categoryTypeInfo'] = trans_counter_to_key_value(Counter(category_type_list))
        for school in school_list:
            schoolInfo_addition(resume_analysis['schoolInfo'], get_school_category(school))
        # detect zero
        detect_zero = True
        for value in resume_analysis['schoolInfo'].values():
            if value!=0:
                detect_zero = False
                break
        if detect_zero:
            resume_analysis['schoolInfo'] = {}
        return resume_analysis


class CompanyRecruitmentStatisticHandler(DBBasedHandler):
    def get(self):
        company_name = self.get_argument('companyName')
        from_date_text = self.get_argument('from', '20000101')
        to_date_text = self.get_argument('to', '20200101')
        if from_date_text==None or from_date_text=='':
            from_date_text = '20000101'
        if to_date_text==None or to_date_text=='':
            to_date_text = '20200101'
        data = self.build_return_data(
            companyName=company_name,
            fromText=from_date_text,
            toText=to_date_text
        )
        if len(data):
            self.set_header('Content-Type', 'application/json;chartset=UTF-8')
            self.write(json.dumps(data, ensure_ascii=False, sort_keys=True, indent=4))
        else:
            raise HTTPError(404, 'not found.')

    def build_return_data(self, **kwargs):
        """

        :rtype : object
        """
        company_name = kwargs['companyName']
        from_time = kwargs['fromText']
        to_time = kwargs['toText']
        recruitment_info = dict()
        if from_time == '' or to_time == '':
            raise HTTPError(400, 'time format failed')
        else:
            # salary average
            query_sql = """
                        select avg(salary) from recruitment
                        where to_char(release_time,'YYYYMMDD')>=%s
                        and to_char(release_time,'YYYYMMDD')<=%s
                        and company_name=%s
                        """
            values = (from_time, to_time, company_name)
            result = pg.select_sql(query_sql, values)
            if result:
                recruitment_info['salaryAvg'] = result[0]
            else:
                recruitment_info['salaryAvg'] = 0

            # scale
            query_sql = """
                        select distinct(scale) from recruitment
                        where to_char(release_time,'YYYYMMDD')>=%s
                        and to_char(release_time,'YYYYMMDD')<=%s
                        and company_name=%s
                        """
            values = (from_time, to_time, company_name)
            result = pg.select_sql(query_sql, values)
            if result:
                recruitment_info['scale'] = result[0]
            else:
                recruitment_info['scale'] = ''

            # working years avg
            query_sql = """
                        select avg(working_years) from recruitment
                        where to_char(release_time,'YYYYMMDD')>=%s
                        and to_char(release_time,'YYYYMMDD')<=%s
                        and company_name=%s
                        """
            values = (from_time, to_time, company_name)
            result = pg.select_sql(query_sql, values)
            if result:
                recruitment_info['workingYearsAvg'] = result[0]
            else:
                recruitment_info['workingYearsAvg'] = 0

            # location
            query_sql = """
                        select city as location,count(city) as num from recruitment
                        where to_char(release_time,'YYYYMMDD')>=%s
                        and to_char(release_time,'YYYYMMDD')<=%s
                        and company_name=%s and city<>'' and city is not null
                        group by city order by num
                        """
            values = (from_time, to_time, company_name)
            result = pg.select_all_sql(query_sql, values)
            if result:
                recruitment_info['location'] = [item['location'] for item in result]
            else:
                recruitment_info['location'] = []

            # category
            query_sql = """
                        select category,count(category) as count,
                        round(count(category)::numeric/
                        (select count(*)
                        from recruitment
                        where to_char(release_time,'YYYYMMDD')>=%s
                        and to_char(release_time,'YYYYMMDD')<=%s
                        and company_name=%s)::numeric,2)
                        as pre
                        from recruitment
                        where to_char(release_time,'YYYYMMDD')>=%s
                        and to_char(release_time,'YYYYMMDD')<=%s
                        and company_name=%s and category<>'' and category is not null
                        group by category order by pre desc
                        """
            values = (from_time, to_time, company_name, from_time, to_time, company_name)
            result = pg.select_all_sql(query_sql, values)
            if result:
                recruitment_info['categoryInfo'] = []
                for item in result:
                    recruitment_info['categoryInfo'].append({
                        'name': item['category'],
                        'value': float(item['pre'])
                    })
            else:
                recruitment_info['categoryInfo'] = []

            # degree
            query_sql = """
                        select degree,count(degree) as count,
                        round(count(degree)::numeric/
                        (select count(*)
                        from recruitment
                        where to_char(release_time,'YYYYMMDD')>=%s
                        and to_char(release_time,'YYYYMMDD')<=%s
                        and company_name=%s)::numeric,2)
                        as pre
                        from recruitment
                        where to_char(release_time,'YYYYMMDD')>=%s
                        and to_char(release_time,'YYYYMMDD')<=%s
                        and company_name=%s
                        group by degree order by pre desc
                        """
            values = (from_time, to_time, company_name, from_time, to_time, company_name)
            result = pg.select_all_sql(query_sql, values)
            if result:
                recruitment_info['degreeInfo'] = []
                for item in result:
                    recruitment_info['degreeInfo'].append({
                        'name': item['degree'],
                        'value': float(item['pre'])
                    })
            else:
                recruitment_info['categoryInfo'] = []

            # similar company salary avg
            _query = """
                      SELECT AVG(salary) FROM recruitment WHERE company_name IN
                      (SELECT b.company_name FROM recruitment_monitor_company a,
                      recruitment_monitor_company b WHERE a.company_name=%s AND
                      a.scale = b.scale AND a.location = b.location AND
                      b.industry &&a.industry
                      GROUP BY b.company_name) AND to_char(release_time,'YYYYMMDD')>=%s
                      and to_char(release_time,'YYYYMMDD')<=%s
                     """
            _values = (company_name,from_time,to_time)
            result = pg.select_sql(_query, _values)
            if result and result[0]:
                recruitment_info['similarCompanyAvgSalary'] = result[0]
            else:
                recruitment_info['similarCompanyAvgSalary'] = 0

            # salaryDis info
            query_sql = """
                        with t1(salary) as (select salary from recruitment
                        where to_char(release_time,'YYYYMMDD')>=%s
                        and to_char(release_time,'YYYYMMDD')<=%s
                        and company_name=%s)
                        select
                        (select round(count(salary)::numeric/(select count(*) from t1)::numeric,3) from t1 where
                        t1.salary<2000) as range_1,
                        (select round(count(salary)::numeric/(select count(*) from t1)::numeric,3) from t1 where
                        t1.salary between 2000 and 3999) as range_2,
                        (select round(count(salary)::numeric/(select count(*) from t1)::numeric,3) from t1 where
                        t1.salary between 4000 and 7999) as range_3,
                        (select round(count(salary)::numeric/(select count(*) from t1)::numeric,3) from t1 where
                        t1.salary between 8000 and 10000) as range_4,
                        (select round(count(salary)::numeric/(select count(*) from t1)::numeric,3) from t1 where
                        t1.salary>10000) as range_5
                        """
            values = (from_time, to_time, company_name)
            try:
                result = pg.select_sql(query_sql, values)
            except:
                result = None
            if result:
                recruitment_info['salaryDis'] = {
                    '2k以下': float(result['range_1']),
                    '2k-4k': float(result['range_2']),
                    '4k-8k': float(result['range_3']),
                    '8k-10k': float(result['range_4']),
                    '10k以上': float(result['range_5'])
                }
                # detect zero
                detect_zero = True

                for value in recruitment_info['salaryDis'].values():
                    if value!=0:
                        detect_zero=False
                        break
                if detect_zero:
                    recruitment_info['salaryDis'] = {}
            else:
                recruitment_info['salaryDis'] = {}
            return recruitment_info


class CompanyRecruitmentCompareHandler(DBBasedHandler):
    def get(self):
        company_name = self.get_argument('companyName')
        from_date_text = self.get_argument('from', '')
        to_date_text = self.get_argument('to', '')
        if from_date_text==None or from_date_text=='':
            from_date_text = '20160101'
        if to_date_text==None or to_date_text=='':
            to_date_text = '20160107'
        data = self.build_return_data(
            companyName=company_name,
            fromText=from_date_text,
            toText=to_date_text
        )
        if len(data):
            self.set_header('Content-Type', 'application/json;chartset=UTF-8')
            self.write(json.dumps(data, ensure_ascii=False, sort_keys=True, indent=4))
        else:
            raise HTTPError(404, 'not found.')

    def build_return_data(self, **kwargs):
        """

        :rtype : object
        """
        company_name = kwargs['companyName']
        from_text = kwargs['fromText']
        to_text = kwargs['toText']
        data = {}
        from_date = datetime.datetime.strptime(from_text, '%Y%m%d')
        to_date = datetime.datetime.strptime(to_text, '%Y%m%d')
        while from_date <= to_date:
            from_date_text = from_date.strftime('%Y%m%d')
            data[from_date_text] = {}
            # category type part
            query_sql = """
                        select * from recruitment
                        where company_name=%s and
                        to_char(release_time,'YYYYMMDD')=%s and
                        category_type not in
                        (select category_type from recruitment where
                        company_name=%s and to_char(release_time,'YYYYMMDD')<%s)
                        """
            values = (company_name, from_date_text, company_name, from_date_text)
            result = pg.select_all_sql(query_sql, values)
            if len(result):
                data[from_date_text]['addCategoryTypeList'] = self.trans_resullt_fromat(result)
            else:
                data[from_date_text]['addCategoryTypeList'] = []

            # category part
            query_sql = """
                        select * from recruitment
                        where company_name=%s and
                        to_char(release_time,'YYYYMMDD')=%s and
                        category not in
                        (select category from recruitment where
                        company_name=%s and to_char(release_time,'YYYYMMDD')<%s)
                        """
            values = (company_name, from_date_text, company_name, from_date_text)
            result = pg.select_all_sql(query_sql, values)
            if len(result):
                data[from_date_text]['addCategoryList'] = self.trans_resullt_fromat(result)
            else:
                data[from_date_text]['addCategoryList'] = []

            # location part
            query_sql = """
                        select * from recruitment
                        where company_name=%s and
                        to_char(release_time,'YYYYMMDD')=%s and
                        city not in
                        (select city from recruitment where
                        company_name=%s and to_char(release_time,'YYYYMMDD')<%s)
                        """
            values = (company_name, from_date_text, company_name, from_date_text)
            result = pg.select_all_sql(query_sql, values)
            if len(result):
                data[from_date_text]['addLocationList'] = self.trans_resullt_fromat(result)
            else:
                data[from_date_text]['addLocationList'] = []

            # salary part
            query_sql = """
                        select
                        case
                        when a.avg<(b.avg- b.stddev_pop) then 'Lower'
                        when a.avg>(b.avg- b.stddev_pop) then 'Higher'
                        when a.avg>(b.avg- b.stddev_pop) and  a.avg<(b.avg- b.stddev_pop)  then 'Normal'
                        else
                            'Empty'
                        end as status, a.avg, b.avg from
                        (select avg(salary)from recruitment where
                         company_name=%s and
                         to_char(release_time,'YYYYMMDD')=%s) a,
                        (select avg(salary) ,stddev_pop(salary) from recruitment where
                         company_name=%s and
                         to_char(release_time,'YYYYMMDD')<%s) b
                        """
            values = (company_name, from_date_text, company_name, from_date_text)
            result = pg.select_all_sql(query_sql, values)
            if len(result):
                data[from_date_text]['salaryDetect'] = {
                    'des':result[0][0],
                    'salaryAvg':result[0][1],
                    'preSalaryAvg':result[0][2],
                }
            else:
                data[from_date_text]['salaryDetect'] = {}

            # working years part
            query_sql = """
                        select
                        case
                        when a.avg<(b.avg- b.stddev_pop) then 'Lower'
                        when a.avg>(b.avg- b.stddev_pop) then 'Higher'
                        when a.avg>(b.avg- b.stddev_pop) and  a.avg<(b.avg- b.stddev_pop)  then 'Normal'
                        else
                            'Empty'
                        end as status, a.avg, b.avg from
                        (select avg(working_years)from recruitment where
                         company_name=%s and
                         to_char(release_time,'YYYYMMDD')=%s) a,
                        (select avg(working_years) ,stddev_pop(working_years) from recruitment where
                         company_name=%s and
                         to_char(release_time,'YYYYMMDD')<%s) b
                        """
            values = (company_name, from_date_text, company_name, from_date_text)
            result = pg.select_all_sql(query_sql, values)
            if len(result):
                data[from_date_text]['workingYearsDetect'] = {
                    'des':result[0][0],
                    'workingYearsAvg':result[0][1],
                    'preWorkingYearsAvg':result[0][2],
                }
            else:
                data[from_date_text]['workingYearsDetect'] = {}

            from_date += datetime.timedelta(days=1)

        # remove empty data
        for key,item in data.items():
            if len(item['addCategoryTypeList'])==0 and len(item['addCategoryList'])==0 and len(item['addLocationList'])==0:
                data.pop(key)
        return data

    def trans_resullt_fromat(self, result_list):
        return_result = []
        for item in result_list:
            return_result.append({
                'id': item.get('id',''),
                'url': item.get('url',''),
                'companyName': item.get('company_name', ''),
                'categoryType': item.get('category_type', ''),
                'category': item.get('category', ''),
                'salary': item.get('salary'),
                'city': item.get('city', ''),
                'workingYears': item.get('working_years'),
                'degree': item.get('degree',''),
                'jobTitle': item.get('job_title', ''),
                'description': item.get('description',''),
                'source': item.get('source',''),
                'companyUrl': item.get('company_url',''),
                'entity': item.get('item',''),
                'welfare': item.get('welfare',[]),
                'keywords': item.get('keywords',[]),
                'logo': item.get('logo',''),
                'scale': item.get('scale',''),
                'releaseTime':  item.get('release_time','').strftime('%Y-%m-%d'),
                'updateTime': item.get('update_time').strftime('%Y-%m-%d'),
                'jobNature': item.get('job_nature',''),
                'industry': item.get('industry',''),
                'position': item.get('position',''),
                'requireNum': item.get('require_num',''),
                'address': item.get('address',''),
                'salaryText': item.get('salary_text','')
            })
        return return_result


class CompanyResumeCompareHandler(DBBasedHandler):

    def get(self):
        company_name = self.get_argument('companyName')
        from_date_text = self.get_argument('from', '')
        to_date_text = self.get_argument('to', '')
        if from_date_text==None or from_date_text=='':
            from_date_text = '20160101'
        if to_date_text==None or to_date_text=='':
            to_date_text = '20160107'
        data = self.build_return_data(
            companyName=company_name,
            fromText=from_date_text,
            toText=to_date_text
        )
        if len(data):
            self.set_header('Content-Type', 'application/json;chartset=UTF-8')
            self.write(json.dumps(data, ensure_ascii=False, sort_keys=True, indent=4))
        else:
            raise HTTPError(404, 'not found.')

    def build_return_data(self, **kwargs):
        """

        :rtype : object
        """
        company_name = kwargs['companyName']
        from_text = kwargs['fromText']
        to_text = kwargs['toText']
        data = {}
        from_date = datetime.datetime.strptime(from_text, '%Y%m%d')
        to_date = datetime.datetime.strptime(to_text, '%Y%m%d')
        while from_date <= to_date:
            from_date_text = from_date.strftime('%Y%m%d')
            data[from_date_text] = []
            # resume list
            query = """
                    select category,count(category) from resume where
                    company_name=%s and
                    to_char(end_time,'YYYYMMDD')=%s group by category
                    """
            values = (company_name, from_date_text)
            result = pg.select_all_sql(query, values)
            # data[from_date_text]['categoryDis'] = []
            for item in result:
                # data[from_date_text].append({
                #     'id': item.get('id',''),
                #     'url': item.get('url',''),
                #     'companyName': item.get('company_name', ''),
                #     'workingYears': item.get('working_years'),
                #     'degree': item.get('degree',''),
                #     'sex': item.get('sex',''),
                #     'location': item.get('location',''),
                #     'jobTitle': item.get('job_title', ''),
                #     'detail': item.get('detail',''),
                #     'preCompany': item.get('pre_company',''),
                #     'nextCompany': item.get('next_company',''),
                #     'releaseTime':  item.get('release_time','').strftime('%Y-%m-%d'),
                #     'beginTime': item.get('begin_time').strftime('%Y-%m-%d'),
                #     'endTime': item.get('end_time').strftime('%Y-%m-%d'),
                #     'dob': item.get('dob').strftime('%Y-%m-%d'),
                #     'school': item.get('school',''),
                #     'major': item.get('major',''),
                # })
                data[from_date_text].append({
                    'name': item[0],
                    'value': item[1]
                })
            from_date += datetime.timedelta(days=1)

        # remove empty data
        for key,item in data.items():
            if len(item)==0:
                data.pop(key)
        return data


class CompanyRecruitmentTimeStepHandler(DBBasedHandler):

    def get(self):
        company_name = self.get_argument('companyName')
        year_text = self.get_argument('year', datetime.datetime.now().year)
        month_text = self.get_argument('month', datetime.datetime.now().month)
        if year_text==None or year_text=='':
            year_text =  datetime.datetime.now().year
        if month_text==None or month_text=='':
            month_text = datetime.datetime.now().month
        data = self.build_return_data(
            companyName=company_name,
            yearText=year_text,
            monthText=month_text
        )
        if len(data):
            self.set_header('Content-Type', 'application/json;chartset=UTF-8')
            self.write(json.dumps(data, ensure_ascii=False, sort_keys=True, indent=4))
        else:
            raise HTTPError(404, 'not found.')

    def build_return_data(self, **kwargs):
        company_name = kwargs['companyName']
        year = kwargs['yearText']
        month = kwargs['monthText']
        time_text_list = self.get_time_step(company_name,year,month)
        recruitment_info = {}
        for time_text in time_text_list:
            recruitment_info[time_text] = {}

            # category_type dis
            query_sql = """
                    with a(category_type,count,avg) as (select category_type, count(category_type), round(avg(salary)::numeric,1) from recruitment
                    where to_char(release_time,'YYYYMM')=%s and company_name=%s
                    group by category_type),
                    b(category_type) as (select distinct category_type from category_info)
                    select b.category_type,
                    case
                    when a.count is null then 0
                    else
                    a.count
                    end,a.avg
                    from b left join a on a.category_type=b.category_type
                        """
            values = (time_text, company_name)
            result = pg.select_all_sql(query_sql, values)
            if result:
                recruitment_info[time_text]['categoryTypeInfo'] = []
                for item in result:
                    recruitment_info[time_text]['categoryTypeInfo'].append({
                        'name': item[0],
                        'sum': item[1],
                        'salaryAvg': float(str(item[2])) if item[2] else None
                    })
            else:
                recruitment_info[time_text]['categoryTypeInfo'] = []

        return recruitment_info



    def get_time_step(self, company_name, year_text, month_text):
        query = """
                select release_time from recruitment
                where company_name=%s
                order by release_time limit 1
                """
        values = (company_name,)
        result = pg.select_all_sql(query, values)
        time_step_list = list()
        if len(result):
            begin_time = result[0][0]
            end_time = datetime.datetime.strptime(str(year_text)+str(month_text), '%Y%m')
            time_delta = datetime.timedelta(days=30)
            while end_time>=begin_time or (end_time.year==begin_time.year and end_time.month==begin_time.month):
                time_step_list.append(end_time.strftime("%Y%m"))
                if end_time.month==3:
                    end_time -= datetime.timedelta(days=28)
                else:
                    end_time -= time_delta
            if end_time.month <= begin_time.month and len(time_step_list)>0:
                time_step_list.append(begin_time.strftime("%Y%m"))
            return time_step_list
        else:
            return time_step_list

if __name__ == '__main__':
    pass
